To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96
Chapter 29 Providing for Data Integrity and Triggers
When designing an application or a database, it is important that you design not only for performance and functionality but for data integrity as well. A database that does not guarantee data integrity is not worth having. The database and application are only as good as the data stored within them.
An accounting application that does not guarantee that the books balance does not have much value to the accountant who uses it. It is important to guarantee the integrity of your database. By placing integrity constraints on your database to enforce business rules, you guarantee that these rules are not violated.
It is very difficult to foresee every possible event in an application; it is even more difficult to foresee the outcome of ad-hoc changes to the database. By using integrity constraints, your data will be protected. This chapter looks at some ways you can protect your dataand how to do this in an optimal manner.
Integrity Constraints
It is usually necessary to enforce business rules in your application and database to protect your data. By using the Oracle integrity constraints, you can protect your data efficiently. Enforcing business rules from within Oracle can be more efficient than enforcing the same rules within your application for several reasons:
- SQL statements can be reduced. By allowing Oracle to enforce these business rules with integrity constraints, the application does not have to issue additional SQL statements. This reduces application overhead and network traffic.
- Internal operations are faster. Because integrity constraints are internal operations, they are naturally faster and more efficient.
- Application development is simplified. When you enforce the business rules within the database, multiple applications or parts of the same application need not duplicate the logic of the business rules.
This is not to say that the entire application has to rely on internal Oracle operations for data validations and bounds checking. In some cases, the application itself can handle these operations more efficiently; in general, however, Oracle integrity constraints are more efficient.
The following sections explain how Oracle integrity constraints can be used to enforce business rules and ensure data integrity.
Referential Integrity
Possibly the most common use of constraints is to enforce referential integrity. Referential integrity is used to guarantee that a column value that references another table exists in the other table. Referencing a value in another table that does not exist can cause major data integrity problems. Consider the following example.
The DOGS table introduced in earlier chapter contains a column that describes the breed of the dog (see Table 29.1). This value is actually a numeric value that references a row in the BREEDS table (see Table 29.2). If the DOGS table references a value for the dogs breed that does not exist in the BREEDS table, an unknown result occurs.
Table 29.1 The DOGS Table
|
DOGNAME
| AGE
| BREED
| OWNER
|
|
Dash
| 6
| 1
| Jones
|
Chip
| 4
| 1
| Jones
|
Rigby
| 3
| 3
| Smith
|
Duncan
| 5
| 2
| Miller
|
Rufus
| 5
| 3
| Smith
|
Splash
| 3
| 4
| Blake
|
Piper
| 6
| 1
| Turner
|
Pierce
| 6
| 1
| King
|
Shasta
| 9
| 5
| Adams
|
Teller
| 1
| 1
| King
|
Spots
| 3
| 2
| Ward
|
B.J.
| 3
| 4
| Allan
|
Ginger
| 4
| 5
| Turner
|
Jessie
| 10
| 7
| Wilson
|
Ruff
| 9
| 8
| King
|
Velvet
| 8
| 9
| Wilson
|
Ty
| 4
| 10
| Jones
|
Cotton
| 6
| 8
| Atkins
|
Angel
| 6
| 8
| McArthur
|
Provo
| 5
| 6
| Smith
|
Jenny
| 7
| 6
| Durell
|
Daphne
| 6
| 7
| Bench
|
Bug
| 3
| 5
| Durell
|
Sammie
| 8
| 3
| Turner
|
Bubba
| 3
| 4
| Pike
|
|
Table 29.2 The BREEDS Table
|
BREED
| BREED NAME
| DESCRIPTION
|
|
1
| Border Collie
| Very intelligent and happy
|
2
| Sheltie
| Energetic and willing to please
|
3
| Jack Russell Terrier
| High energy and active
|
4
| Golden Retriever
| Loves to play
|
5
| All American (Mix)
| Anything at all
|
6
| Great Pyrenees
| Big and friendly
|
7
| Irish Setter
| Hunting instinct
|
8
| Toy Poodle
| Lap dog
|
9
| Beagle
| Good nose for tracking
|
10
| Greyhound
| Fast and sleek
|
|
|